
clear all

// This file uses Census of Governments data to compute county-level fiscal outcomes

// Aggregates spending to the county level
// Uses govid to fips crosswalks
// Spending in a county includes county governments and many types of sub-county local and special governments

//2017 
// Raw data for 2017 were accessed at : https://www.census.gov/programs-surveys/apes/data/datasetstables/2017.html
	// Download: 2017FinEstDAT_02202020modp_pu.txt

// Raw data for 2012 and 2007 were accssed at: https://www2.census.gov/programs-surveys/gov-finances/datasets/historical/
	// Download: IndFin12a.txt and IndFin07a.txt
	
// Raw data fro GovID to FIPS codes were accessed at: https://www.census.gov/govs/www/02PubUsedoc_GovOrg.html#F1
	// Download: GOVS_to_FIPS_Codes_State_&_County_2007
	// Download: 2012_GOVS_COUNTY_to_FIPS_COUNTY.xls
	
	// raw data can also be found here: https://www.census.gov/programs-surveys/cog/data/historical-data.html
	
// I created a data dictionary, included in the /Data/input/ 

// The datasets relatively large files, set location here:

global rawdata_2017 "SET PATH HERE"
global rawdata_2007_2012 "SET PATH HERE"

cd "$rawdata_2017"
infile using "$InputPath/GovFin_2017.dct", using("2017FinEstDAT_02202020modp_pu.txt") clear 

collapse (sum) amount , by(id item)
tempfile cog2017
save `cog2017'

infile using "$InputPath/GovFin_2017_IDs.dct", using("Fin_GID_2017.txt") clear 

merge 1:m id using `cog2017'
drop _m

reshape wide amount, i(id) j(item) string

destring fstate fcounty, replace

gen type=substr(id,3,1)

//aggregation methods accessed here: https://www.census.gov/govs/classification/index.html, download 
// "methodology_for_summary_tabulations.xls"

egen totalrevenue=rowtotal(amountB01	amountB21	amountB22	amountB30	amountB42	amountB46	amountB50	amountB59	amountB79	amountB80	amountB89	amountB91	amountB92	amountB93	amountB94	amountC21	amountC30	amountC42	amountC46	amountC50	amountC79	amountC80	amountC89	amountC91	amountC92	amountC93	amountC94	amountD21	amountD30	amountD42	amountD46	amountD50	amountD79	amountD80	amountD89	amountD91	amountD92	amountD93	amountD94	amountT01	amountT09	amountT10	amountT11	amountT12	amountT13	amountT14	amountT15	amountT16	amountT19	amountT20	amountT21	amountT22	amountT23	amountT24	amountT25	amountT27	amountT28	amountT29	amountT40	amountT41	amountT50	amountT51	amountT53	amountT99	amountA01	amountA03	amountA09	amountA10	amountA12	amountA16	amountA18	amountA21	amountA36	amountA44	amountA45	amountA50	amountA59	amountA60	amountA61	amountA80	amountA81	amountA87	amountA89	amountU01	amountU11	amountU20	amountU30	amountU40	amountU41	amountU50	amountU95	amountU99	amountA90	amountA91	amountA92	amountA93	amountA94	amountX01	amountX02	amountX05	amountX08	amountY01	amountY02	amountY11	amountY12	amountY51	amountY52), missing

egen totaltaxes=rowtotal(amountT01	amountT09	amountT10	amountT11	amountT12	amountT13	amountT14	amountT15	amountT16	amountT19	amountT20	amountT21	amountT22	amountT23	amountT24	amountT25	amountT27	amountT28	amountT29	amountT40	amountT41	amountT50	amountT51	amountT53	amountT99), missing

egen totalfedigrevenue=rowtotal(amountB01	amountB21	amountB22	amountB30	amountB42	amountB46	amountB50	amountB59	amountB79	amountB80	amountB89	amountB91	amountB92	amountB93	amountB94)

egen totalstateigrevenue=rowtotal(amountC21	amountC30	amountC42	amountC46	amountC50	amountC79	amountC80	amountC89	amountC91	amountC92	amountC93	amountC94), missing

egen totlocaligrev=rowtotal(	  amountD21	amountD30	amountD42	amountD46	amountD50	amountD79	amountD80	amountD89	amountD91	amountD92	amountD93	amountD94), missing

egen totalexpenditure=rowtotal(amountE01	amountE03	amountE04	amountE05	amountE12	amountE16	amountE18	amountE21	amountE22	amountE23	amountE24	amountE25	amountE29	amountE31	amountE32	amountE36	amountE44	amountE45	amountE50	amountE52	amountE59	amountE60	amountE61	amountE62	amountE66	amountE74	amountE75	amountE77	amountE79	amountE80	amountE81	amountE85	amountE87	amountE89	amountE90	amountE91	amountE92	amountE93	amountE94	amountI89	amountI91	amountI92	amountI93	amountI94	amountJ19	amountL67	amountJ68	amountJ85	amountX11	amountX12	amountY05	amountY06	amountY14	amountY53	amountF01	amountF03	amountF04	amountF05	amountF12	amountF16	amountF18	amountF21	amountF22	amountF23	amountF24	amountF25	amountF29	amountF31	amountF32	amountF36	amountF44	amountF45	amountF50	amountF52	amountF59	amountF60	amountF61	amountF62	amountF66	amountF77	amountF79	amountF80	amountF81	amountF85	amountF87	amountF89	amountF90	amountF91	amountF92	amountF93	amountF94	amountG01	amountG03	amountG04	amountG05	amountG12	amountG16	amountG18	amountG21	amountG22	amountG23	amountG24	amountG25	amountG29	amountG31	amountG32	amountG36	amountG44	amountG45	amountG50	amountG52	amountG59	amountG60	amountG61	amountG62	amountG66	amountG77	amountG79	amountG80	amountG81	amountG85	amountG87	amountG89	amountG90	amountG91	amountG92	amountG93	amountG94	amountL01	amountL04	amountL05	amountL12	amountL18	amountL23	amountL25	amountL29	amountL32	amountL36	amountL44	amountL52	amountL59	amountL60	amountL61	amountL62	amountL66	amountL67	amountL79	amountL80	amountL81	amountL87	amountL89	amountL91	amountL92	amountL93	amountL94	amountM01	amountM04	amountM05	amountM12	amountM18	amountM21	amountM23	amountM24	amountM25	amountM29	amountM30	amountM32	amountM36	amountM44	amountM50	amountM52	amountM59	amountM60	amountM61	amountM62	amountM66	amountM67	amountM68	amountM79	amountM80	amountM81	amountM87	amountM89	amountM91	amountM92	amountM93	amountM94	amountQ12	amountQ18	amountS67	amountS89), missing

egen directexpenditure=rowtotal (amountE01	amountE03	amountE04	amountE05	amountE12	amountE16	amountE18	amountE21	amountE22	amountE23	amountE24	amountE25	amountE29	amountE31	amountE32	amountE36	amountE44	amountE45	amountE50	amountE52	amountE59	amountE60	amountE61	amountE62	amountE66	amountE74	amountE75	amountE77	amountE79	amountE80	amountE81	amountE85	amountE87	amountE89	amountE90	amountE91	amountE92	amountE93	amountE94	amountF01	amountF03	amountF04	amountF05	amountF12	amountF16	amountF18	amountF21	amountF22	amountF23	amountF24	amountF25	amountF29	amountF31	amountF32	amountF36	amountF44	amountF45	amountF50	amountF52	amountF59	amountF60	amountF61	amountF62	amountF66	amountF77	amountF79	amountF80	amountF81	amountF85	amountF87	amountF89	amountF90	amountF91	amountF92	amountF93	amountF94	amountG01	amountG03	amountG04	amountG05	amountG12	amountG16	amountG18	amountG21	amountG22	amountG23	amountG24	amountG25	amountG29	amountG31	amountG32	amountG36	amountG44	amountG45	amountG50	amountG52	amountG59	amountG60	amountG61	amountG62	amountG66	amountG77	amountG79	amountG80	amountG81	amountG85	amountG87	amountG89	amountG90	amountG91	amountG92	amountG93	amountG94	amountX11	amountX12	amountY05	amountY06	amountY14	amountY53	amountJ19	amountJ67	amountJ68	amountJ85	amountI89	amountI91	amountI92	amountI93	amountI94), missing 

egen correctdirectexp=rowtotal(amountE04	amountF04	amountG04	amountE05	amountF05	amountG05), missing

egen totaleducdirectexp=rowtotal(amountE12	amountF12	amountG12	amountE16	amountF16	amountG16	amountE18	amountF18	amountG18	amountJ19	amountE21	amountF21	amountG21), missing

egen totalutiltotalexp=rowtotal(amountE91	amountF91	amountG91	amountI91	amountE92	amountF92	amountG92	amountI92	amountE93	amountF93	amountG93	amountI93	amountE94	amountF94	amountG94	amountI94) , missing

egen unempcomptotalexp=rowtotal(amountY05	amountY06) , missing

egen totaldebtoutstanding=rowtotal(amount44T	amount49U	amount64V) , missing

egen fireprotdirectexp=rowtotal(amountE24	amountF24	amountG24) , missing

egen judicialdirectexpend=rowtotal(amountE25	amountF25	amountG25) , missing

egen healthdirectexpend=rowtotal(amountE32	amountF32	amountG32) , missing

egen totalhospitaldirexp=rowtotal(amountE36	amountF36	amountG36) , missing

egen totalhighwaysdirexp=rowtotal(amountE44	amountF44	amountG44	amountE45 amountF45	amountG45) , missing

egen houscomdirectexp=rowtotal(amountE50	amountF50	amountG50) , missing

egen librariesdirectexp=rowtotal(amountE52	amountF52	amountG52) , missing

egen naturalrestotalexp=rowtotal(amountE59	amountF59	amountG59) , missing

egen parkingtotalexpend=rowtotal(amountE60	amountF60	amountG60) , missing

egen policeprottotalexp=rowtotal(amountE62	amountF62	amountG62) , missing

egen publicwelfdirectexp=rowtotal(amountJ67	amountJ68	amountE74	amountE75	amountE77	amountF77	amountG77	amountE79	amountF79	amountG79) , missing

egen publicwelfcashasst=rowtotal(amountJ67	amountJ68) , missing

egen fedigrutilities=rowtotal(amountB91	amountB92	amountB93	amountB94) , missing

egen stateigrutilities=rowtotal(amountC91	amountC92	amountC93	amountC94) , missing

egen localigrutilities=rowtotal(amountD91	amountD92	amountD93	amountD94) , missing

egen igexptostategovt=rowtotal(amountL01	amountL04	amountL05	amountL12	amountL18	amountL23	amountL25	amountL29	amountL32	amountL36	amountL44	amountL50	amountL52	amountL59	amountL60	amountL61	amountL62	amountL66	amountL67	amountL79	amountL80	amountL81	amountL87	amountL89	amountL91	amountL92	amountL93	amountL94), missing

egen igexptolocalgovts=rowtotal(amountM01	amountM04	amountM05	amountM12	amountM18	amountM21	amountM23	amountM24	amountM25	amountM29	amountM30	amountM32	amountM36	amountM44	amountM50	amountM52		amountM59	amountM60	amountM61	amountM62	amountM66	amountM67	amountM68	amountM79	amountM80	amountM81	amountM87	amountM89	amountM91	amountM92	amountM93	amountM94   amountQ12 amountQ18), missing

egen igexptofederalgovt=rowtotal(amountS67	amountS89), missing

egen genrevownsources=rowtotal(amountA01	amountA03	amountA09	amountA10	amountA12	amountA16	amountA18	amountA21	amountA36	amountA44	amountA45	amountA50	amountA59	amountA60	amountA61	amountA80	amountA81	amountA87	amountA89	amountT01	amountT09	amountT10	amountT11	amountT12	amountT13	amountT14	amountT15	amountT16	amountT19	amountT20	amountT21	amountT22	amountT23	amountT24	amountT25	amountT27	amountT28	amountT29	amountT40	amountT41	amountT50	amountT51	amountT53	amountT99	amountU01	amountU11	amountU20	amountU30	amountU40	amountU41	amountU50	amountU95	amountU99),missing 

rename  amountB01 fedigrairtransport
rename  amountB21 fedigreducation
rename  amountB22 fedigrempsecadm
rename  amountB30 fedigrgensupport
rename  amountB42 fedigrhealthhos
rename  amountB46 fedigrhighways
rename  amountB50 fedigrhouscomdev
rename  amountB59 fedigrnaturalres
rename  amountB79 fedigrpublicwelf
rename  amountB80 fedigrsewerage
rename  amountB89 fedigrother
rename  amountC21 stateigreducation
rename  amountC30 stateigrothgensup
rename  amountC42 stateigrhealthhos
rename  amountC46 stateigrhighways
rename  amountC50 stateigrhouscomdev
rename  amountC79 stateigrpublicwelf
rename  amountC80 stateigrsewerage
rename  amountC89 stateigrother
rename  amountD21 localigreducation
rename  amountD30 localigrothgensup
rename  amountD42 localigrhealthhos
rename  amountD46 localigrhighways
rename  amountD50 localigrhouscomdev
rename  amountD79 localigrpublicwelf
rename  amountD80 localigrsewerage
rename  amountD89 localigrother
rename  amountE62 policeprotdirectexp
rename  amountG62 policeprotcapoutlay
rename  amountF62 policeprotconstruct
rename  amountZ00 totalsalarieswages


rename amountM01 airtransiglocalgovts 
rename amountM04 correctiglocgovts
 
// 2017 separately sums transfers to school districts vs. local governments, 2012 and 2017 do not
egen elemeduciglocalgovts=rowtotal(amountM12 amountQ12)
egen higherediglocgovts=rowtotal(amountM18 amountQ18 )

rename amountM21 educneciglocalgovts 
rename amountM23 finadminiglocalgovts 
rename amountM24 fireprotiglocalgovts 
rename amountM25 judicialiglocalgovts 
rename amountM29 censtaffiglocalgovts 

rename amountM32 healthiglocalgovts 
rename amountM36 totalhospitaliglocgovts 
rename amountM44 regularhwyiglocgovts 
rename amountM50 houscomiglocgovts 
rename amountM52 librariesiglocalgovts 
rename amountM59 naturalresiglocgovts 
rename amountM60 parkingiglocalgovts 
rename amountM61 parksreciglocgovts 
rename amountM62 policeprotiglocgovts 
rename amountM66 protinspiglocalgovts 
rename amountM67 welfcategiglocgovts 
rename amountM68 welfcashiglocalgovts 
rename amountM79 welfneciglocalgovts 
rename amountM80 sewerageiglocalgovts 
rename amountM81 swmgmtiglocalgovts 
rename amountM87 watertransiglocgovts 
rename amountM89 generalneciglocgovts 
rename amountM94 transitsubiglocgovts 


drop amount*


tab type, gen (type)

gen z=igexptolocalgovts if type=="0"
egen state_tot_igr_local=mean(z), by(fstate)

gen zz=totalexpenditure if type=="0"
egen state_tot_exp=mean(zz), by(fstate) 

gen zzz=totalrevenue if type=="0"
egen state_tot_rev=mean(zzz), by(fstate)

local A totalrevenue totaltaxes totalfedigrevenue fedigrairtransport fedigreducation fedigrempsecadm fedigrgensupport fedigrhealthhos fedigrhighways fedigrhouscomdev fedigrnaturalres fedigrpublicwelf fedigrsewerage fedigrother ///
 totalstateigrevenue stateigreducation stateigrothgensup stateigrhealthhos stateigrhighways stateigrhouscomdev stateigrpublicwelf stateigrsewerage stateigrother totlocaligrev localigreducation localigrothgensup localigrhealthhos localigrhighways localigrhouscomdev localigrpublicwelf localigrsewerage localigrother ///
 totalexpenditure directexpenditure correctdirectexp totaleducdirectexp igexptostategovt igexptolocalgovts igexptofederalgovt totalsalarieswages genrevownsources

// vars from file B
local B fireprotdirectexp judicialdirectexpend  healthdirectexpend totalhospitaldirexp totalhighwaysdirexp houscomdirectexp librariesdirectexp naturalrestotalexp parkingtotalexpend policeprotdirectexp policeprottotalexp policeprotcapoutlay policeprotconstruct publicwelfdirectexp publicwelfcashasst 


//vars from file C
local C totalutiltotalexp unempcomptotalexp totaldebtoutstanding 

/// state-level vars
local state_vars state_tot_igr_local state_tot_exp state_tot_rev  sewerageiglocalgovts swmgmtiglocalgovts educneciglocalgovts finadminiglocalgovts fireprotiglocalgovts judicialiglocalgovts censtaffiglocalgovts healthiglocalgovts librariesiglocalgovts parkingiglocalgovts protinspiglocalgovts welfcashiglocalgovts welfneciglocalgovts airtransiglocalgovts elemeduciglocalgovts watertransiglocgovts generalneciglocgovts higherediglocgovts totalhospitaliglocgovts regularhwyiglocgovts transitsubiglocgovts houscomiglocgovts naturalresiglocgovts parksreciglocgovts policeprotiglocgovts welfcategiglocgovts correctiglocgovts

tempfile base 
save `base', replace

keep if type=="0"  //create state-level dataset
keep  fstate state_tot_igr_local state_tot_exp state_tot_rev `state_vars'  
gen year=2017

foreach i of varlist `state_vars' {
replace `i'=1000*`i'
}

tempfile state 
save `state'

use `base', clear 
drop if type=="0" // drop state-level governments
drop type


collapse (sum) `A' `B' `C' type* (mean) state_tot_igr_local state_tot_exp state_tot_rev  , by(fstate fcounty)

/*
1-Digit Type of Government Code (Ind_Fin UserGuide.xls)
   0 = State government                4 = Special district
   1 = County                          5 = School district (independent only)
   2 = Municipality                    6 = Federal Government
   3 = Township                        
*/

//add 1 for var type* (e.g., type1=state)
rename (type2 type3 type4 type5 type6) (ngov_county ngov_munic ngov_town ngov_special ngov_school)

// All amounts reported in thousands of dollars
//	 Convert to dollars
foreach i of varlist `A' `B' `C' state_tot_igr_local state_tot_exp state_tot_rev  {
replace `i'=1000*`i'
}

gen year=2017


tempfile g2017
save `g2017', replace

cd "$rawdata_2007_2012"

//2012
import delim using IndFin12a.txt, clear

tempfile govfin
save `govfin', replace

import delim IndFin12b.Txt, clear
merge 1:1 id using `govfin', gen(m1)
save `govfin', replace


import delim IndFin12c.Txt, clear
merge 1:1 id using `govfin', gen(m2)

gen year=2012

save `govfin', replace


//2007
import delim using IndFin07a.Txt, clear
tempfile govfin7

save `govfin7'

import delim using IndFin07b.Txt, clear
merge 1:1 id using `govfin7', gen(m1)
save `govfin7', replace

import delim IndFin07c.Txt, clear
merge 1:1 id using `govfin7', gen(m2)

gen year=2007

append using `govfin', force
save `govfin', replace


// vars from file A
local ID year id type statecode typecode name county 
local A totalrevenue population totaltaxes  totaligrevenue totalfedigrevenue fedigrairtransport fedigreducation fedigrempsecadm fedigrgenrevshar fedigrgensupport fedigrhealthhos fedigrhighways fedigrtransitsub fedigrhouscomdev fedigrnaturalres fedigrpublicwelf fedigrsewerage fedigrother ///
 totalstateigrevenue stateigreducation stateigrtaxrelief stateigrothgensup stateigrhealthhos stateigrhighways stateigrtransitsub stateigrhouscomdev stateigrpublicwelf stateigrsewerage stateigrother totlocaligrev localigrinterschoolaid localigrothereducation  ///
 localigrothgensup localigrhealthhos localigrhighways localigrtransitsub localigrhouscomdev localigrpublicwelf localigrsewerage localigrother ///
 totalexpenditure directexpenditure correctdirectexp totaleducdirectexp elemeducigtostate igexptostategovt igexptolocalgovts igexptofederalgovt totalsalarieswages genrevownsources

// vars from file B
local B fireprotdirectexp judicialdirectexpend  healthdirectexpend totalhospitaldirexp totalhighwaysdirexp houscomdirectexp librariesdirectexp naturalrestotalexp parkingtotalexpend policeprotdirectexp policeprottotalexp policeprotcapoutlay policeprotconstruct publicwelfdirectexp publicwelfcashasst ///
 higheredigtost educnecigtostate finadminigtostate fireprotigtostate judicialigtostate censtaffigtostate healthigtostate  totalhospitaligtostate hospotherigtostate transitsubigtosta houscomigtostate librariesigtostate naturalresigtosta parkingigtostate parksrecigtosta policeprotigtosta protinspigtostate welfcategigtostate welfnecigtostate regularhwyigtosta

//vars from file C
local C totalutiltotalexp unempcomptotalexp totaldebtoutstanding /// 
sewerageiglocalgovts swmgmtigtostate watertransiglocgovts generalnecigtost

// vars for state-level ig exp 
local state_vars  sewerageiglocalgovts swmgmtiglocalgovts educneciglocalgovts finadminiglocalgovts fireprotiglocalgovts judicialiglocalgovts censtaffiglocalgovts healthiglocalgovts librariesiglocalgovts parkingiglocalgovts protinspiglocalgovts welfcashiglocalgovts welfneciglocalgovts airtransiglocalgovts elemeduciglocalgovts watertransiglocgovts generalneciglocgovts higherediglocgovts totalhospitaliglocgovts  regularhwyiglocgovts transitsubiglocgovts houscomiglocgovts naturalresiglocgovts parksreciglocgovts policeprotiglocgovts welfcategiglocgovts correctiglocgovts


gen hasschool=(yeardepsch!="BB" & yeardepsch!="")
egen everschool=max(hasschool), by(id)

gen countyschool=everschool*(type==1)

tab type, gen (type)

gen z=igexptolocalgovts if typecode==0
egen state_tot_igr_local=mean(z), by(statecode year)

gen zz=totalexpenditure if typecode==0
egen state_tot_exp=mean(zz), by(statecode year) 

gen zzz=totalrevenue if typecode==0
egen state_tot_rev=mean(zzz), by(statecode year)

tempfile base 
save `base', replace 

keep if typecode==0  //create state-level dataset
keep  year fipscodestate `state_vars' state_tot_igr_local state_tot_exp state_tot_rev 
rename fipscodestate fstate 
append using `state'
cd "$OutputPath/"
export delim GovFin_state.csv, replace

use `base', clear 

drop if county==0 //drops state and fed gov
drop if zerodata==1 //drops forms returned with 0 for all finance variables (n==12,630)



collapse (sum) `A' `B' `C' countyschool type* (first) id  (mean) state_tot_igr_local state_tot_exp state_tot_rev  , by(year statecode county)

/*
1-Digit Type of Government Code (Ind_Fin UserGuide.xls)
   0 = State government                4 = Special district
   1 = County                          5 = School district (independent only)
   2 = Municipality                    6 = Federal Government
   3 = Township                        
*/
//add 1 for var type* (e.g., type1=state)
rename (type2 type3 type4 type5 type6) (ngov_county ngov_munic ngov_town ngov_special ngov_school)
drop type1 //(no observations after county collapse)

// All amounts reported in thousands of dollars
//	 Convert to dollars
foreach i of varlist `A' `B' `C' state_tot_igr_local state_tot_exp state_tot_rev {
replace `i'=1000*`i'
}


// To use GID-FIPS crosswalk, need first 1 or 2 digits (state), drop type=1 (2 or 3rd digit) and keep next 3 (county)
// on GID codes: https://www.census.gov/govs/www/02PubUsedoc_GovOrg.html#F1
tostring id, replace
gen govid_state=""
replace govid_state=substr(id,1,1) if statecode<10 //data import drops the leading zero for state codes <10
replace govid_state=substr(id,1,2) if statecode>=10 & statecode!=.

gen govid_county=""
replace govid_county=substr(id,3,3) if statecode<10 //last three digits are redundant for county government, remove to merge with FIPS crosswalk
replace govid_county=substr(id,4,3) if statecode>=10 & statecode!=.

egen govid=concat(govid_state govid_county)
destring govid, replace

drop id statecode govid_state govid_county
tempfile govfin
save `govfin', replace


clear
import excel "$InputPath/GOVS_to_FIPS_Codes_State_&_County_2007.xls", firstrow sheet("County Codes") 

rename (B F H) (govid fstate fcounty)

drop if fcounty=="" //drops census areas, no fips codes
drop if govid=="" | govid=="GOVS" | govid=="ID"
keep govid fstate fcounty
destring govid fstate fcounty, replace
gen year=2007

merge 1:m year govid using `govfin', gen(m07)
save `govfin', replace


clear
import excel "$InputPath/2012_GOVS_COUNTY_to_FIPS_COUNTY.xls", firstrow sheet("GOVS_TO_FIPS")
rename (FIPS_STATE	FIPS_COUNTY	COUNTY_NAME) (fstate fcounty county_gov)
egen govid=concat(GOVS_STATE GOVS_COUNTY)
destring govid, replace
keep govid fstate fcounty county_gov
destring govid fstate fcounty, replace
gen year=2012

merge 1:m year govid using `govfin', gen(m12)
save `govfin', replace


drop m07 m12

// append
append using `g2017' 

// Rename variables
rename (totalutiltotalexp unempcomptotalexp totaldebtoutstanding fireprotdirectexp judicialdirectexpend healthdirectexpend totalhospitaldirexp totalhighwaysdirexp houscomdirectexp librariesdirectexp naturalrestotalexp parkingtotalexpend policeprottotalexp policeprotdirectexp policeprotcapoutlay policeprotconstruct publicwelfdirectexp publicwelfcashasst) ///
(exp_util exp_unemp debt_tot exp_fire exp_judicial exp_health exp_hospital exp_highway exp_housecom exp_library exp_natres exp_parking exp_pol_total exp_police exp_pol_capital exp_pol_construction exp_welfare exp_cashasst)

rename (totalrevenue totaltaxes totalfedigrevenue totalstateigrevenue totlocaligrev totalexpenditure directexpenditure correctdirectexp totaleducdirectexp) ///
(rev_tot tax_tot rev_fed rev_state rev_locgov exp_tot exp_direct exp_correction exp_edu)


drop if fcounty==. | fcounty==0
drop if fstate==51 & fcounty==189 //no such county in vermont

drop type1 typecode

cd "$OutputPath/"
save GovFin_allincounty, replace
